<?php
	include("/../../data.php");
	session_start();
	if(($_SESSION['db_is_logged_in'] == false) || ($_SESSION['profile'] != 'admin'))
		header("location:index.php");
	
	$pass = "";	
	$connect=mysql_connect($DatabaseServer,$DatabaseUsername,$DatabasePassword) or die("can't connect this database");
	mysql_select_db($DatabaseName,$connect);
	
    $sql="select * from staff where username='".$_SESSION['username'] ."'";
    $query=mysql_query($sql);
	
	if(mysql_num_rows($query)==0) {
			header("location:index.php");     
    } 
	else{
		$row=mysql_fetch_array($query);         
		$pass = $row["password"];
	}
	
	if(isset($_COOKIE['InputUsername'])){
		if( ($_SESSION['db_is_logged_in'] == true) && ( $_COOKIE["InputUsername"]!=$pass))
		{
			//header("location:index.php");
		}
	}
	
	if( ($_SESSION['db_is_logged_in'] == true)&&( $_SESSION['password']!=$pass))
	{
		header("location:index.php");
	}
?>

<html>
<head>
    <meta http-equiv="Content-Type" content="text/html; charset=UTF-8" />
    <title>Import Excel to MySql</title>
</head>
<body>



<?php
	function format_ten($hoten)
	{
		$hoten = trim($hoten);
		$hotens = explode(" ",$hoten);
		$n = count($hotens);
		$ten = '';
		if($n == 1)
		{
			$ten = $hotens[0];
		}
		
		if($n > 1)
		{
			$ten = $hotens[$n-1];
		}
		return $ten;
	}
	
	function format_ho($hoten)
	{
		$hoten = trim($hoten);
		$hotens = explode(" ",$hoten);
		$n = count($hotens);
		$ho = '';
		if($n>=2)
		{
			$ho = $hotens[0];
		}
		return $ho;
	}
	
	function format_tenlot($hoten)
	{
		$hoten = trim($hoten);
		$hotens = explode(" ",$hoten);
		$n = count($hotens);
		$tenlot = '';
		
		if($n >= 2)
		{
			for($i=1; $i < $n-1; ++$i)
			{
					$tenlot = $tenlot .$hotens[$i] .' ';
			}
			$tenlot = trim($tenlot);
		}
		
		return $tenlot;
	}
		
	function format_ngaysinh($MicrosoftTimestamp)
	{
		$MicrosoftTimestamp = trim($MicrosoftTimestamp);
		$temp = explode("/",$MicrosoftTimestamp);
		if(count($temp) == 0){
			//Chuyển Microsoft Timestamp to UnixTimestamp
			$UnixTimestamp = ($MicrosoftTimestamp - 25569) * 86400;
			$datetimeStr = date('Y-m-d H:i:s', $UnixTimestamp);
			
			$temp = explode("/",$datetimeStr);
			$ngay = $temp[0];
			$thang = $temp[1];
			$nam = $temp[2];
			$ngaythangnam = new Datetime($thang .'/' .$ngay .'/' .$nam);
			
			return $ngaythangnam;
		}
		else{
			$ngay = $temp[0];
			$thang = $temp[1];
			$nam = $temp[2];
			$ngaythangnam = new Datetime($thang .'/' .$ngay .'/' .$nam);
			
			//echo date_format($ngaythangnam, 'd-m-Y');
			return $ngaythangnam;
		}
	}
	
	function convert_to_string($MicrosoftTimestamp)
	{
		$MicrosoftTimestamp = trim($MicrosoftTimestamp);
		$temp = explode("/",$MicrosoftTimestamp);
		
		//echo count($temp);
		
		
		if(count($temp) <= 1){
			//Chuyển Microsoft Timestamp to UnixTimestamp
			$UnixTimestamp = ($MicrosoftTimestamp - 25569) * 86400;
			$datetimeStr = date('d/m/Y', $UnixTimestamp);
			//echo $datetimeStr;
			return $datetimeStr;
		}
		else{
			return $MicrosoftTimestamp;
		}
	}
	
	function format_ngay($thoigian)
	{
		$thoigian = convert_to_string($thoigian);
		$temp = explode("/",$thoigian);
		return $temp[0];
	}
	
	function format_thang($thoigian)
	{
		$thoigian = convert_to_string($thoigian);
		$temp = explode("/",$thoigian);
		return $temp[1];
	}
	
	function format_nam($thoigian)
	{
		$thoigian = convert_to_string($thoigian);
		$temp = explode("/",$thoigian);
		return $temp[2];
	}
	
	function format_string($string)
	{ 
		$string = str_replace("'", "" , $string);
		return $string;
	}
	
	function divide_string($string, $number)
	{
		$string = trim($string);
		$strings = explode('/',$string);
		$n = count($strings);
		$string = '';
		
		if($n >= $number)
		{
			$string = $strings[$number - 1];
		}
		
		return $string;
	}
	
	function query_sql($sql)
	{
		$result = mysql_query($sql);

		if (!$result) {
			echo "Could not successfully run query ($sql) from DB: " . mysql_error();
			exit;
		}

		if (mysql_num_rows($result) == 0) {
			echo "No rows found, nothing to print so am exiting";
			exit;
		}
		
		
		return $result;
	}
	
	
	function insertDatabase($cmtText_Staff){
		if (!mysql_query($cmtText_Staff))
		{
			die('Error: ' . mysql_error($conn));
		}
		else
		{
			echo '<p>' ."1 record staff added" .'</p>';	
		}		
	}
	
	function insertOneDatabase($cmtText){
		if (!mysql_query($cmtText))
		{
			die('Error: ' . mysql_error($conn));
		}
		else
		{
			echo '<p>' ."1 record added" .'</p>';	
		}		
	}
	
	//Function Main
	if ($_FILES["file"]["error"] > 0)
	{
		echo "Error: " . $_FILES["file"]["error"] . "<br>";
	}
	else
		{
			/** Access Database **/
			$conn = mysql_connect($DatabaseServer,$DatabaseUsername,$DatabasePassword);
			mysql_set_charset('utf8',$conn);
			if (!$conn) {
				echo "Unable to connect to DB: " . mysql_error();
				exit;
			}

			if (!mysql_select_db($DatabaseName)) {
				echo "Unable to select opensisdata: " . mysql_error();
				exit;
			}
		
		
			
			//echo $_POST["username"] . "<br>";
			
			echo "Upload: " . $_FILES["file"]["name"] . "<br>";
			echo "Type: " . $_FILES["file"]["type"] . "<br>";
			echo "Size: " . ($_FILES["file"]["size"] / 1024) . " kB<br>";
			echo "Stored in: " . $_FILES["file"]["tmp_name"];
			$location = $_FILES["file"]["tmp_name"];
	
			error_reporting(E_ALL);
			ini_set('display_errors', TRUE);
			ini_set('display_startup_errors', TRUE);
			date_default_timezone_set('Europe/London');

			define('EOL',(PHP_SAPI == 'cli') ? PHP_EOL : '<br />');

			/*
			//Import Library
			require_once dirname(__FILE__) . '/../Classes/PHPExcel.php';
			require_once '/../Classes/PHPExcel/IOFactory.php';
			$objPHPExcel = PHPExcel_IOFactory::load($location);
*/
			//Import Library
			require_once '../Classes/PHPExcel.php';
			require_once '../Classes/PHPExcel/IOFactory.php';
			$objPHPExcel = PHPExcel_IOFactory::load($location);
			
			//$worksheet = $objPHPExcel->getWorksheetIterator()->getCellCollection[0];
			$i = 0; //SHEET = 0;
			foreach ($objPHPExcel->getWorksheetIterator() as $worksheet) {
			if($i == 0)
			{
				$worksheetTitle     = $worksheet->getTitle();
				$highestRow         = $worksheet->getHighestRow(); // e.g. 10
				$highestColumn      = $worksheet->getHighestColumn(); // e.g 'F'
				$highestColumnIndex = PHPExcel_Cell::columnIndexFromString($highestColumn);
				$nrColumns = ord($highestColumn) - 64;
				
				
				echo "<br>The worksheet ".$worksheetTitle." has ";
				echo $nrColumns . ' columns (A-' . $highestColumn . ') ';
				echo ' and ' . $highestRow . ' row.';
				//echo '<br>Data: <table border="1"><tr>';
				
				for ($row = 2; $row <= $highestRow; ++ $row) {
				//for ($row = 2; $row <=2; ++ $row) {	
				
					/**for ($col = 0; $col < $highestColumnIndex; ++ $col) {
						$cell = $worksheet->getCellByColumnAndRow($col, $row);
						$val = $cell->getValue();
						$dataType = PHPExcel_Cell_DataType::dataTypeForValue($val);
						echo '<p>' . $col  . '.  ' . $val .'</p>';	
					}**/
					$countcell = 0;
					
					for ($col = 0; $col < $highestColumnIndex; ++ $col) {
						$cell = $worksheet->getCellByColumnAndRow($col, $row);
						if($cell == "")
						{
							++ $countcell; 
						}
						
					}
					//echo '<p>' .'========================' .$row .'========================' .$countcell .'</p>';
					
					
					
					if($countcell <= 10){
					
					echo '<p>' ."INSERT TEACHER " .($row-1) .'</p>';
					
					$stt = 					$worksheet->getCellByColumnAndRow(0, $row);
					$maGiangVien= 				$worksheet->getCellByColumnAndRow(1, $row);
					$hoten= 				$worksheet->getCellByColumnAndRow(2, $row);
					$dienthoai= 				$worksheet->getCellByColumnAndRow(3, $row);
					$hocham=				$worksheet->getCellByColumnAndRow(4, $row);
					$hocvi=  			$worksheet->getCellByColumnAndRow(5, $row);
					$chuyenmon= 		$worksheet->getCellByColumnAndRow(6, $row); 
					$montungday= 				$worksheet->getCellByColumnAndRow(7, $row); 
					$diachi= 				$worksheet->getCellByColumnAndRow(8, $row); 
					$sotaikhoan= 					$worksheet->getCellByColumnAndRow(9, $row); 
					$nganhang=	 	$worksheet->getCellByColumnAndRow(10, $row);
					$chinhanhnganhang= 		$worksheet->getCellByColumnAndRow(11, $row);
					$cmnd= 			$worksheet->getCellByColumnAndRow(12, $row);
					$masothue= 			$worksheet->getCellByColumnAndRow(13, $row);
					$ngaysinh= 					$worksheet->getCellByColumnAndRow(14, $row);
					$gioitinh= 		$worksheet->getCellByColumnAndRow(15, $row);
					$ghichu= 			$worksheet->getCellByColumnAndRow(16, $row);
					$email = "";
					
					/**____________________ Xử lý họ tên ____________________**/
					//echo '<p>' .'Xử lý họ tên' .'</p>';
					//echo '<p>' . $hoten .'</p>';
					$hoten = mb_convert_case($hoten, MB_CASE_TITLE, "UTF-8");
					$ho= format_ho($hoten) .' ' .format_tenlot($hoten);
					//$tenlotsinhvien= format_tenlot($hoten);
					$ten= format_ten($hoten);
					//echo '<p>' . $ho .'</p>';
					//echo '<p>' . $tenlotsinhvien .'</p>';
					//echo '<p>' . $ten .'</p>';
					/**____________________==========End==========____________________**/
					
					
					/**____________________ Xử lý điện thoại ____________________**/
					//echo '<p>' .'Xử lý điện thoại' .'</p>';
					//echo '<p>' . $dienthoai .'</p>';
					$dienthoai1 = divide_string($dienthoai, 1);
					$dienthoai2 = divide_string($dienthoai, 2);
					$dienthoai3 = divide_string($dienthoai, 3);
					//echo '<p>' . $dienthoai1 .'</p>';
					//echo '<p>' . $dienthoai2 .'</p>';
					//echo '<p>' . $dienthoai3 .'</p>';
					/**____________________==========End==========____________________**/
					
					
					/**____________________ Xử lý ngày sinh ____________________**/
					//echo '<p>' .'Xử lý ngày sinh' .'</p>';
					//echo '<p>' . $ngaysinh .'</p>';
					
					
					
					$ngay = format_ngay($ngaysinh);
					$thang = format_thang($ngaysinh);
					$nam = format_nam($ngaysinh);
					$ngaythangnam = new Datetime($thang .'/' .$ngay .'/' .$nam);
					//echo '<p>' . date_format($ngaythangnam, 'Y-m-d') .'</p>';
					/**____________________==========End==========____________________**/
					
					
					/**____________________ Xử lý giới tính ____________________**/
					//echo '<p>' .'Xử lý giới tính' .'</p>';
					//echo '<p>' . $dataType .'</p>';
					if($gioitinh == 'Nam')
					{
						$gioitinh = 'Male';
					}
					else
					{
						$gioitinh = 'Female';
					}
					//echo '<p>' . $gioitinh .'</p>';
					/**____________________==========End==========____________________**/
					
					
					
					/**____________________ Xử lý text____________________**/
					//echo '<p>' .'Xử lý địa chỉ' .'</p>';
					$hocham = 			format_string($hocham);
					$hocvi = 			format_string($hocvi);
					$chuyenmon= 		format_string($chuyenmon); 
					$montungday= 		format_string($montungday); 
					$diachi= 			format_string($diachi); 
					$sotaikhoan= 		format_string($sotaikhoan); 
					$nganhang=	 		format_string($nganhang);
					$chinhanhnganhang= 	format_string($chinhanhnganhang);
					$cmnd= 				format_string($cmnd);
					$masothue= 			format_string($masothue);
					$ghichu= 			format_string($ghichu);
					//echo '<p>' . $hocham .'</p>';
					//echo '<p>' . $hocvi .'</p>';
					//echo '<p>' . $chuyenmon .'</p>';
					//echo '<p>' . $montungday .'</p>';
					//echo '<p>' . $diachi .'</p>';
					//echo '<p>' . $sotaikhoan .'</p>';
					//echo '<p>' . $nganhang .'</p>';
					//echo '<p>' . $chinhanhnganhang .'</p>';
					//echo '<p>' . $cmnd .'</p>';
					//echo '<p>' . $masothue .'</p>';
					//echo '<p>' . $ghichu .'</p>';
					/**____________________==========End==========____________________**/
					
					
					/**____________________ Xử lý mật khẩu ____________________**/
					//echo '<p>' .'Xử lý mật khẩu' .'</p>';
					//$matkhausinhvien = md5($idHCMUS);
					//echo '<p>' .$matkhausinhvien .'</p>';
					/**____________________==========End==========____________________**/
					
					
					/********************************************************************************************************/
					/********************************************************************************************************/
					/********************************************************************************************************/
					
					/**____________________ Get staff_id ____________________**/
					$sql = "SELECT * FROM staff ORDER BY staff_id DESC limit 1";
					$result = query_sql($sql);
					
					$staff_id = 0;
					while ($data = mysql_fetch_assoc($result)) {
						$staff_id = $data["staff_id"];
					}
					mysql_free_result($result);
					
					//echo '<p>' .'staff_id: ' .$staff_id .'</p>';
					/**____________________==========End==========____________________**/
					
					
					/**____________________ Get student_enrollment_id ____________________**/
					$sql = "SELECT * FROM student_enrollment ORDER BY id DESC limit 1";
					$result = query_sql($sql);
					
					$student_enrollment_id = 0;
					while ($data = mysql_fetch_assoc($result)) {
						$student_enrollment_id = $data["id"];
					}
					mysql_free_result($result);
					
					//echo '<p>' .'student_enrollment_id: ' .$student_enrollment_id .'</p>';
					/**____________________==========End==========____________________**/
					
					
					/********************************************************************************************************/
					/********************************************************************************************************/
					/********************************************************************************************************/
					
					
					//$_student_id = $student_id + 1;
					//$_address_id = 1 + $address_id;
					//$_join_address_id = 1 + $join_address_id;
					$_staff_id = 1 + $staff_id;
					$_student_enrollment_id = 1 + $student_enrollment_id;


					$_school_id = 1;                                                                 //SCHOOL
					$_school_id2 = 2;
					$_calendar_id = 2;
					$_grade_id = 1;
									
					$_next_school = "";
					$_prim_student_relation = "Father";

					$_syear = "2013";
					
					
					//////////////////////////////////////////
					
					
					
					/** get id by lopOther 
						get id by lopHCMUS **/
						
					
					$_lopOther_id = "";
					$_lopHCMUS_id = "";
					$_status_id = "";
		
					$username = "";
					$password = "";
					$profile = "teacher";
					$failed_login = "0";
					$profile_id = "2";

					$cmtText_Staff = "INSERT INTO `staff`(`staff_id`, `current_school_id`, `first_name`, `last_name`, `username`, `password`, `phone`, `email`, `profile`, `failed_login`, `profile_id`, `rank`, `degree`, `speciality`, `subjects_teach`, `account_number`, `bank`, `bank_address`, `identity_card`, `tax_code`, `birthdate`, `gender`, `note`) VALUES (" .$_staff_id .", '"  .$_school_id ."', '" .$ten ."', '" .$ho ."', '" .$username ."', '" .$password ."', '" .$dienthoai1 ."', '" .$email ."', '" .$profile ."', '" .$failed_login ."', '" .$profile_id ."', '" .$hocham ."', '" .$hocvi ."', '" .$chuyenmon ."', '" .$montungday ."', '" .$sotaikhoan ."', '" .$nganhang ."', '" .$chinhanhnganhang ."', '" .$cmnd ."', '" .$masothue ."', '" .$masothue ."', '" .$gioitinh ."', '" .$ghichu ."')";

					//echo '<p>' .$cmtText_Staff .'</p>';
					
					/**____________________ Get and insert Staff ____________________**/
					$sql = "SELECT * FROM schools";
					$result = query_sql($sql);
					
					$student_enrollment_id = 0;
					while ($data = mysql_fetch_assoc($result)) {
						$_school_id = $data["id"];
						$_syear = $data["syear"];
						//echo '<p>' .'SCHOOL ID: ' .$_school_id .'</p>';
						//echo '<p>' .'SYEAR: ' .$_syear .'</p>';
						
						$cmdText_Staff_School_RelationShip = "INSERT INTO `staff_school_relationship`(`staff_id`, `school_id`, `syear`) VALUES ('" .$_staff_id ."', '" .$_school_id ."', '" .$_syear ."')";
						//echo '<p>' .$cmdText_Staff_School_RelationShip .'</p>';
					
						insertOneDatabase($cmdText_Staff_School_RelationShip);
					}
					mysql_free_result($result);
					
					/**____________________==========End==========____________________**/
								
					insertDatabase($cmtText_Staff);

				}
				}
				$i++;
			}
			}
			echo '<p>' . 'Closed Database' .'</p>';
			/** Close Connect **/
		}
?>


</body>
</html>